package datos;

import java.awt.Image;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import javax.imageio.ImageIO;
import javax.imageio.ImageReadParam;
import javax.imageio.ImageReader;
import javax.imageio.stream.ImageInputStream;
import javax.swing.JOptionPane;
import negocios.Prenda;
import negocios.Usuario;

public class DatabaseManager {
    private Connection connection;
    private Savepoint svpt;
    private ResultSet resultSet = null;
    private Statement statement = null;
    
    public DatabaseManager() {
        try {
            String url = "jdbc:postgresql://localhost/TrabajoFinal?user=postgres&password=pac_08ev";
            System.out.println(url);
            connection = DriverManager.getConnection(url);
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace(); }
     }

    public void commit() {
        try {
            connection.commit();
            svpt = connection.setSavepoint();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() {
        return connection;  }

    public void openConnectionTo(String url) {
        try {
            connection = DriverManager.getConnection(url, "postgres", "clave");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
  
   public boolean grabarFOTO(Usuario miuser, String ruta) {
        FileInputStream fis = null;
        try {
           
             File file = new File(ruta);
             fis = new FileInputStream(file);
             PreparedStatement pstm = connection.prepareStatement("update usuario set foto=? where cedula='" + miuser.getCedula() + "'");
             pstm.setBinaryStream(1, fis,(int) file.length());
             pstm.execute();
             connection.commit();
             pstm.close();
         return true;
        } catch (FileNotFoundException e) {
             System.out.println(e.getMessage());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
               fis.close();
             } catch (IOException e) {
               System.out.println(e.getMessage());
             }
        }
        return false;
        }  
    
      public ArrayList Lista_Users(){
         ArrayList todos = new ArrayList();  
         BaseDeDatosDB b=new BaseDeDatosDB();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT cedula,apodo,id_genero,fecha_nacimiento,foto FROM usuario " );
          while (resultSet.next())
          {  
             Usuario miUser = new Usuario();
             miUser.setCedula( resultSet.getString("cedula") ) ;
             miUser.setApodo( resultSet.getString("apodo") ) ;
             miUser.setGenero(b.buscarGenero(resultSet.getInt("id_genero"))); 
             miUser.setFecha_nacimiento(resultSet.getDate("fecha_nacimiento"));
             
             try { //antes de agregar el campo imagen, realiza la conversion de bytes a JPG
               miUser.setFoto(ConvertirImagen(resultSet.getBytes("foto")));
             } catch (IOException ex) {
               System.err.println(ex.getMessage());
             }
             
            todos.add(miUser);  
          }
       }
       catch (SQLException ex) {
          System.err.println(ex.getMessage());}
       return todos;
    }
      
    private Image ConvertirImagen(byte[] bytes) throws IOException {
    ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
    Iterator readers = ImageIO.getImageReadersByFormatName("jpeg");    
    ImageReader reader = (ImageReader) readers.next();
    Object source = bis;
    ImageInputStream iis = ImageIO.createImageInputStream(source);
    reader.setInput(iis, true);
    ImageReadParam param = reader.getDefaultReadParam();
    return reader.read(0, param);
 }
     public boolean grabarFOTOPrenda(Prenda miprenda, String ruta) {
        FileInputStream fis = null;
        try {
             File file = new File(ruta);
             fis = new FileInputStream(file);
             PreparedStatement pstm = connection.prepareStatement("update prenda set foto=? where nombre='" + miprenda.getNombre() + "'");
             pstm.setBinaryStream(1, fis,(int) file.length());
             pstm.execute();
             connection.commit();
             pstm.close();
         return true;
        } catch (FileNotFoundException e) {
             System.out.println(e.getMessage());
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
               fis.close();
             } catch (IOException e) {
               System.out.println(e.getMessage());
             }
        }
        return false;
        } 
      
     public ArrayList Lista_RopasSegunUser(String ced){
         ArrayList todos = new ArrayList();  
         BaseDeDatosDB b=new BaseDeDatosDB();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM prenda where id_usuario= '" + ced + "'");
          while (resultSet.next())
          {  
             Prenda miprenda = new Prenda();
             miprenda.setId( resultSet.getInt("id") ) ;
             miprenda.setTipo_prenda(b.buscarTipo(resultSet.getInt("id_tipo_prenda")));
              miprenda.setNombre( resultSet.getString("nombre") ) ;
            todos.add(miprenda);  
          }
       }
       catch (SQLException ex) {
          System.err.println(ex.getMessage());}
       return todos;
    } 
     public ArrayList Lista_Ropas(){
         ArrayList todos = new ArrayList();  
         BaseDeDatosDB b=new BaseDeDatosDB();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM prenda " );
          while (resultSet.next())
          {  
             Prenda miprenda = new Prenda();
             miprenda.setId( resultSet.getInt("id") ) ;
             miprenda.setNum_usos( resultSet.getInt("num_usos") ) ;
             miprenda.setNum_lavado(/*b.buscarGenero(*/resultSet.getInt("num_lavado")); 
             miprenda.setUltimo_lavado(resultSet.getDate("ultimo_lavado"));
             miprenda.setEstado_prenda( b.buscarEstado(resultSet.getInt("id_estado_prenda")));
             miprenda.setUsuario(b.buscarUsuario(resultSet.getString("id_usuario")));
             miprenda.setFecha(resultSet.getDate("fecha"));
             miprenda.setTipo_prenda(b.buscarTipo(resultSet.getInt("id_tipo_prenda")));
             miprenda.setColor(b.buscarColor(resultSet.getInt("id_color")));
            
             try { //antes de agregar el campo imagen, realiza la conversion de bytes a JPG
               miprenda.setFoto(ConvertirImagen(resultSet.getBytes("foto")));
             } catch (IOException ex) {
               System.err.println(ex.getMessage());
             }
             miprenda.setNombre(resultSet.getString("nombre"));
             // FALTA CALIFICACION
             
            todos.add(miprenda);  
          }
       }
       catch (SQLException ex) {
          System.err.println(ex.getMessage());}
       return todos;
    }  
    
    public void rollBack() {
        try {
            if (svpt != null) {
                connection.rollback(svpt);
                connection.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
